import java.net.UnknownHostException;
import java.util.Date;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mongodb.AggregationOutput;
import com.mongodb.MongoClient;
import com.mongodb.MongoException;
import com.mongodb.WriteConcern;
import com.mongodb.DB;
import com.mongodb.DBCollection;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.mongodb.DBCursor;
import com.mongodb.ServerAddress;

public class MongoNormalTemplate extends Template {
	private MongoClient client;
	private Date lshipdate = null;
	private String rname = "";
	private String psize = "";
	private String ptype = "";
	private Date oorderdate = null;
	private Date oorderdate2 = null;
	private String cmktsegment = "";
	DB database;
	@Override
	public void configure(Map<String, Object> params) {
		try {
			client = new MongoClient();
			database = client.getDB((String) params.get("db_name"));
		} catch (UnknownHostException e) {
			
		}
	}

	@Override
	public Map<String, Object> insertLineItems(int nInsertsLineitemExpected) {
		Map<String, Object> result = new HashMap<String, Object>();
		calculateInsertsExpected(nInsertsLineitemExpected);
		int nInsertsPartsupp, nInsertsPart, nInsertsNation, nInsertsLineitem,
		nInsertsRegion, nInsertsCustomer, nInsertsOrders, nInsertsSupplier;
		nInsertsPartsupp = nInsertsPart = nInsertsNation = nInsertsLineitem = 
		nInsertsRegion = nInsertsCustomer = nInsertsOrders = nInsertsSupplier = 0;
		
		//Minimum 1 Region and 1 Nation
		nInsertsRegionExpected = nInsertsRegionExpected == 0 ? 1 : nInsertsRegionExpected;
		nInsertsNationExpected = nInsertsNationExpected == 0 ? 1 : nInsertsNationExpected;
		
		//Set result vars
		result.put("lineitemExpected", nInsertsLineitemExpected);
		result.put("partsuppExpected", nInsertsPartsuppExpected);
		result.put("partExpected", nInsertsPartExpected);
		result.put("nationExpected", nInsertsNationExpected);
		result.put("regionExpected", nInsertsRegionExpected);
		result.put("customerExpected", nInsertsCustomerExpected);
		result.put("ordersExpected", nInsertsOrdersExpected);
		result.put("supplierExpected", nInsertsSupplierExpected);
		
		//Set vars actual state
		int lastPkRegion = maxValue("region", "_id");
		int lastPkNation = maxValue("nation", "n_nationkey");
		int lastPkCustomer = maxValue("customer", "c_custkey");
		int lastPkSupplier = maxValue("supplier", "s_suppkey");
		int lastPkPart = maxValue("part", "p_partkey");
		int lastPkOrder = maxValue("orders", "o_orderkey");
		int lastCountPartsupp = count("partsupp");
		
		int nPk = lastPkRegion + 1;
		DBCollection oDBCollection = database.getCollection("region");
		for (int i = 0; i < nInsertsRegionExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject().
					append("_id", nPk).
					append("r_name", RandomValues.randomString(32)).
					append("r_comment", RandomValues.randomString(60));
			oDBCollection.insert(oDocument);
			nPk++;
			nInsertsRegion++;
		}
		
		oDBCollection = database.getCollection("nation");
		nPk = lastPkNation + 1;
		for (int i = 0; i < nInsertsNationExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject().
			append(	"_id", 				nPk).
			append(	"r_name", 			RandomValues.randomString(32)).
			append(	"r_regionkey", 		RandomValues.randomInt(lastPkRegion + 1, lastPkRegion + nInsertsRegion)).
			append(	"r_comment", 		RandomValues.randomString(60));
			oDBCollection.insert(oDocument);
			nInsertsNation++;
			nPk++;
		}
		
		nPk = lastPkCustomer + 1;
		oDBCollection = database.getCollection("customer");
		for (int i = 0; i < nInsertsCustomerExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject(
					"c_custkey", 		nPk).
            append(	"c_name", 			RandomValues.randomString(25)).
            append(	"c_address", 		RandomValues.randomString(40)).
            append(	"c_nationkey",		RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsNation)).
            append(	"c_phone", 			RandomValues.randomString(15)).
            append(	"c_acctbal", 		RandomValues.randomFloat(1000, 1100, 2)).
            append(	"c_mktsegment", 	RandomValues.randomString(10)).
            append(	"c_comment",		RandomValues.randomString(60));
			oDBCollection.insert(oDocument);
			nInsertsCustomer++;
			nPk++;
		}

		nPk = lastPkSupplier + 1;
		oDBCollection = database.getCollection("supplier");
		for (int i = 0; i < nInsertsSupplierExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject(
					"_id", 		nPk).
            append(	"s_name", 			RandomValues.randomString(25)).
            append(	"s_address", 		RandomValues.randomString(40)).
            append(	"s_nationkey",		RandomValues.randomInt(lastPkNation + 1, lastPkNation + nInsertsNation)).
            append(	"s_phone", 			RandomValues.randomString(15)).
            append(	"s_acctbal", 		RandomValues.randomFloat(1000, 1100, 2)).
            append(	"s_comment",		RandomValues.randomString(60));
			oDBCollection.insert(oDocument);
			nInsertsSupplier++;
			nPk++;
		}
		
		nPk = lastPkPart + 1;
		oDBCollection = database.getCollection("part");
		for (int i = 0; i < nInsertsPartExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject(
					"_id", 		nPk).
            append(	"p_name", 			RandomValues.randomString(32)).
            append(	"p_mfgr", 			RandomValues.randomString(32)).
            append(	"p_brand",			RandomValues.randomString(32)).
            append(	"p_type", 			RandomValues.randomString(32)).
            append(	"p_size", 			RandomValues.randomInt(1000, 1100)).
            append(	"p_container", 		RandomValues.randomString(32)).
            append(	"p_retailprice", 	RandomValues.randomFloat(1000, 1100, 2)).
            append(	"p_comment",		RandomValues.randomString(32));
			oDBCollection.insert(oDocument);
			nInsertsPart++;
			nPk++;
		}
		
		ArrayList<Pair> partSuppKeys = new ArrayList<Pair>();
		nPk = lastCountPartsupp + 1;
		oDBCollection = database.getCollection("partsupp");
		for (int i = 0; i < nInsertsPartsuppExpected; i++) {
			int regsPart = (nInsertsPartExpected + lastPkPart);
			int nPk2 = (int) (Math.ceil((double) nPk / (double) regsPart));
			int nPk1 = ((nPk - 1) % regsPart)+1;
			partSuppKeys.add(new Pair(nPk2, nPk1));
			BasicDBObject oDocument = new BasicDBObject(
					"_id", 					new BasicDBObject( "ps_partkey", nPk1).append("ps_suppkey", nPk2)).
            append(	"ps_availqty", 			RandomValues.randomInt(1000, 1100)).
            append(	"ps_supplycost", 		RandomValues.randomFloat(1000, 1100, 2)).
            append(	"p_comment",			RandomValues.randomString(100));
			oDBCollection.insert(oDocument);
			nInsertsPartsupp++;
			nPk++;
		}
		
		nPk = lastPkOrder + 1;
		oDBCollection = database.getCollection("order");
		for (int i = 0; i < nInsertsOrdersExpected; i++) {
			BasicDBObject oDocument = new BasicDBObject(
					"_id", 					nPk).
            append(	"o_custkey", 			RandomValues.randomInt(lastPkCustomer + 1, lastPkCustomer + nInsertsCustomer)).
            append(	"o_orderstatus", 		RandomValues.randomString(32)).
            append(	"o_totalprice", 		RandomValues.randomFloat(1000, 1100, 2)).
            append(	"o_orderdate", 			RandomValues.randomDate()).
            append(	"o_orderpriority", 		RandomValues.randomString(8)).
            append(	"o_clerk", 				RandomValues.randomString(32)).
            append(	"o_shippriority", 		RandomValues.randomInt(1000, 1100)).
            append(	"o_comment",			RandomValues.randomString(40));
			oDBCollection.insert(oDocument);
			nInsertsOrders++;
			nPk++;
		}

		int nPkOrders = lastPkOrder + 1;
		oDBCollection = database.getCollection("lineitem");
		for (int i = 0; i < nInsertsOrders; i++) {
			int nLength = partSuppKeys.size();
			for (int j=0; j < nLength; j++) {
				Pair partSuppKey =partSuppKeys.get(j);
				BasicDBObject oDocument = new BasicDBObject(
						"_id",				new BasicDBObject("l_orderkey", nPkOrders).
												append("l_partkey", partSuppKey.a).
												append("l_suppkey", partSuppKey.b)).
                append(	"l_linenumber",		nPkOrders % 5).
                append(	"l_quantity", 		RandomValues.randomInt(1000, 1100)).
                append(	"l_extendedprice", 	RandomValues.randomFloat(1000, 1100, 2)).
                append(	"l_discount", 		RandomValues.randomFloat(1000, 1100, 2)).
                append(	"l_tax",			RandomValues.randomFloat(1000, 1100, 2)).
                append(	"l_returnflag",		RandomValues.randomString(1)).
                append(	"l_linestatus",		RandomValues.randomString(1)).
                append(	"l_shipdate",		RandomValues.randomDate()).
                append(	"l_commitdate",		RandomValues.randomDate()).
                append(	"l_receiptdate",	RandomValues.randomDate()).
                append(	"l_shipinstruct",	RandomValues.randomString(25)).
                append(	"l_shipmode",		RandomValues.randomString(10)).
                append(	"l_comment",		RandomValues.randomString(32));
				oDBCollection.insert(oDocument);
				nInsertsLineitem++;
				nPkOrders++;
				if (nInsertsLineitem == nInsertsLineitemExpected) break;
			}
			if (nInsertsLineitem == nInsertsLineitemExpected) break;
		}	
		
		//Set result vars
		result.put("lineitemRegs", nInsertsLineitem);
		result.put("partsuppRegs", nInsertsPartsupp);
		result.put("partRegs", nInsertsPart);
		result.put("nationRegs", nInsertsNation);
		result.put("regionRegs", nInsertsRegion);
		result.put("customerRegs", nInsertsCustomer);
		result.put("ordersRegs", nInsertsOrders);
		result.put("supplierRegs", nInsertsSupplier);
		
		return result;
	}
	
	private int maxValue(String sCollectionName, String sAttribute) {
		int nResult = 0;
		DBCollection oDBCollection = database.getCollection(sCollectionName);
		BasicDBObject oSort = new BasicDBObject(sAttribute, -1);
		DBCursor oDBCursor = oDBCollection.find().sort(oSort).limit(1);
		if (oDBCursor.hasNext()) {
			oDBCursor.next();
			if (oDBCursor.curr().containsField(sAttribute))
				nResult = (Integer) oDBCursor.curr().get(sAttribute);
		}
		return nResult;
	}
	
	private int count(String sCollectionName) {
		return (int) database.getCollection(sCollectionName).count();
	}

	@Override
	public int query1() {
		/*
		 * SELECT l_returnflag, l_linestatus, sum(l_quantity) as sum_qty," +
		" sum(l_extendedprice) as sum_base_price, sum(l_extendedprice*(1-l_discount)) as "+
		" sum_disc_price, sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,"+
		" avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) "+
		" as avg_disc, count(*) as count_order"+
		" FROM lineitem"+
		" WHERE l_shipdate <= date '"+lshipdate.toString()+"' "+
		" GROUP BY l_returnflag, l_linestatus"+
		" ORDER BY l_returnflag, l_linestatus";
		 */
		int nResults = 0;
		DBCollection oDBCollection = database.getCollection("lineitem");
		BasicDBObject oMatch = new BasicDBObject ("$match", 
				new BasicDBObject("l_shipdate", new BasicDBObject("$lt", lshipdate)));
		BasicDBObject oProjectionFields = new BasicDBObject(
						"l_returnflag", 1);
		oProjectionFields.put("l_linestatus", 1);
		oProjectionFields.put("l_quantity", 1);
		oProjectionFields.put("l_extendedprice", 1);
		oProjectionFields.put("opDiscount", 
				new BasicDBObject("$multiply",
						new Object[]{	"$l_extendedprice", 
										new BasicDBObject("$subtract", new Object[]{1, "$l_discount"})
						}
				)
		);
		oProjectionFields.put("opCharge", 
				new BasicDBObject("$multiply", 
						new Object[]{
							"$l_extendedprice", 
							new BasicDBObject("$subtract", new Object[]{1, "$l_discount"}), 
							new BasicDBObject("$subtract", new Object[]{1, "$l_tax"})
						})
		);
		BasicDBObject oProjection = new BasicDBObject("$project", oProjectionFields);
		BasicDBObject oGroupFields = new BasicDBObject("_id", 
				new BasicDBObject("l_returnflag", "$l_returnflag").append("l_linestatus", "$l_linestatus")).
				append("sum_qty", new BasicDBObject("$sum", "$l_quantity")).
				append("sum_base_price", new BasicDBObject("$sum", "$l_extendedprice")).
				append("sum_disc_price", new BasicDBObject("$sum", "$opDiscount")).
				append("sum_charge", new BasicDBObject("$sum", "$opDiscount")).
				append("avg_qty", new BasicDBObject("$avg", "$l_quantity")).
				append("avg_price", new BasicDBObject("$avg", "$l_extendedprice")).
				append("avg_disc", new BasicDBObject("$avg", "$l_discount")).
				append("count_order", new BasicDBObject("$sum", 1));
				
		BasicDBObject oGroup = new BasicDBObject("$group", oGroupFields);
		BasicDBObject oOrderFields = new BasicDBObject("l_returnflag",1).append("l_linestatus", 1);
		BasicDBObject oOrder = new BasicDBObject("$order", oOrderFields);
		Iterable<DBObject> itObjects = oDBCollection.aggregate(oMatch, oProjection, oGroup).results();
		for (DBObject oResult : itObjects) {
			nResults++;
		}
		return nResults;
		// TODO Auto-generated method stub

	}

	@Override
	public int query2() {
		/*
		 * "SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment" +
			" FROM part, supplier, partsupp, nation, region" +
			" WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND p_size = "+psize+" AND p_type like '%"+ptype+"' AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = '"+rname+"' 
				AND ps_supplycost = (SELECT min(ps_supplycost) FROM partsupp, supplier, nation, region WHERE p_partkey = ps_partkey AND s_suppkey = ps_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = '"+rname+"')"  +
			" ORDER BY s_acctbal desc, n_name, s_name, p_partkey";
		 */
		DBCollection oDBCollection = database.getCollection("region");
		BasicDBObject oQuery = new BasicDBObject("r_name", rname);
		DBCursor oCursor = oDBCollection.find(oQuery);
		Object[] aRegionIds = _getIds(oCursor);
		oQuery = new BasicDBObject("r_name", rname);
		return 0;
		// TODO Auto-generated method stub

	}
	
	private Object[] _getIds(DBCursor oCursor) {
		int i = 0;
		Object[] aResult = new Object[oCursor.count()];
		while(oCursor.hasNext()) {
			aResult[i] = oCursor.next().get("_id");
			i++;
		}
		return aResult;
	}

	@Override
	public int query3() {
		return 0;
		// TODO Auto-generated method stub

	}

	@Override
	public int query4() {
		return 0;
		// TODO Auto-generated method stub

	}

	@Override
	public int deleteData() {
		DB oDb = client.getDB("cbde");
		if (oDb != null) {
			client.dropDatabase("cbde");
		}
		return 0;
	}

	@Override
	public void getDataForQueries() {
		DBObject oDocument = database.getCollection("lineitem").findOne();
		lshipdate = (Date) oDocument.get("l_shipdate");
		oDocument = database.getCollection("part").findOne();
		psize = String.valueOf(oDocument.get("p_size"));
		ptype = String.valueOf(oDocument.get("p_type"));
		oDocument = database.getCollection("region").findOne();
		rname = (String) oDocument.get("r_name");
		DBCursor oCursor = database.getCollection("order").find();
		oCursor.next();
		oorderdate = (Date) oCursor.curr().get("o_orderdate");
		oCursor.next();
		oorderdate2 = (Date) oCursor.curr().get("o_orderdate");
	}

}
